# coding=utf-8

import codecs
import os

from p3_create import get_tab_definition, get_tab_modification

#    print(get_tab_definition(conn, "SCOTT", "EMP"))
#    print(get_tab_modification(conn, "SCOTT", "EMP"))


import cx_Oracle
import datetime
import time

USERNAME = 'chenchang313'
PASSWORD = 'Orange2014'
DATABASE = '10.33.21.166:1530/pif'

SQL = """select owner, table_name, num_rows
  from dba_tables
 where owner not in
       ('AURORA$JIS$UTILITY$', 'CTXSYS', 'DBAQRY', 'DBCONN', 'DBMGR',
        'DBMON', 'DBMONOPR', 'DBQUA', 'DBSNMP', 'DBSTATS', 'DEPLOYOP', 'DIP',
        'DMLBAK', 'EXFSYS', 'FGLPA', 'FOGLIGHT', 'HR', 'I3_ORCL', 'LBACSYS',
        'MDSYS', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OSE$HTTP$ADMIN',
        'OUTLN', 'OVSEE', 'PERFSTAT', 'PUB_QUERY', 'PUB_TEST', 'QS',
        'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 'QS_ES', 'QS_OS', 'QS_WS',
        'QUEST', 'SCOTT', 'SPOTLIGHT', 'SQLTXPLAIN', 'SYS', 'SYSTEM',
        'SYSMAN', 'TOAD', 'TOM', 'TRACESVR', 'TSMSYS', 'VERITAS_I3_ORCL',
        'WKSYS', 'WKSYS', 'WMSYS', 'XDB', 'ORACLE_OCM', 'APPMGR',
        'I3_ORCL_R1')
   and table_name not like 'BIN$%'
 order by num_rows"""

SQLx = """select owner, table_name, num_rows
  from dba_tables
 where owner = 'SCOTT' and table_name='TX'
 order by num_rows"""

def my_error_hanlder(f):
    def _error_handler(msg):
        f.write(msg)
        f.write('\n')
    return _error_handler


def test_db():
    os.environ["NLS_LANG"] = "SIMPLIFIED CHINESE_CHINA.AL32UTF8"
    con = cx_Oracle.connect(USERNAME, PASSWORD, DATABASE)
    cursor = con.cursor()
    cursor.execute(SQL)
    with codecs.open('pif_tables.sql', 'w', "utf-8") as ftab:
        with codecs.open('pif_indexes.sql', 'w', encoding="utf-8") as fidx:
            with codecs.open('error.txt', 'w', encoding="utf-8") as ferr:
                for (owner, table_name, num_rows) in cursor:
                    print(owner + " " + table_name)
                    tab_def = get_tab_definition(con, owner, table_name, my_error_hanlder(ferr))
#                    print(tab_def)
                    ftab.write(tab_def)
                    tab_mod = get_tab_modification(con, owner, table_name)
                    fidx.write(tab_mod)

#        print (r)
    cursor.close()
    con.close()

def test():
    print ('test')
    test_db()

if __name__ == "__main__":
    test()
